set more off
log using inequality_with_predictions_v4 , replace 
foreach y in  60 80 {
*use dataset with predictions (created using qregressions_new)
use renteq_pred_stacked_`y's , clear

keep newid ref_year qyear pred_home 

*merge with full dataset (made from inequality_data_w_util by eliminating all years except 61 and 80-81, respectively) 
***NOTE under keepusing command, I've removed _merge_rnt and n_adj_util_floor, which
***are from the utility/rent adjustment file
merge m:1 newid using data_for_pred_merge`y' , generate(pred_merge) keepusing(ref_year cutenure hflow fdhome d_fdhome fdhomepq2 fdhomecq2 renteq cons5 cons6  vehfinpq vehfincq vehicpq vehiccq cartknpq cartkncq cartkupq cartkucq caropq carocq publictransother automobileops n_tot_vflow1 d_gas  d_othvehic d_autofin d_pubtrans transpq transcq utilites d_utility utilpq utilcq totrentdwe d_rent rendwepq rendwecq tflow2 tflow gas_mo scale wgt20 fam_size perslt18 earncomp occ_recode age_grp ed_type race1 male topcode_inc respstat fincbtax vehq) 

*replace rent equivalent with predictions, put into nominal dollars
replace renteq=pred_home if (cutenure==1 & ref_year==1961)
replace renteq=pred_home if ((cutenure==1|cutenure==2|cutenure==3) & (ref_year==1972 | ref_year==1973))
replace renteq=pred_home if ((cutenure==1 | cutenure==2 | cutenure==3) & qyear>=801 & qyear<=845) 

gen double hflow_old = hflow 

replace hflow=renteq*3 if ((cutenure==1 | cutenure==2 | cutenure==3) & qyear>=801 & qyear<=845)
replace hflow=renteq*12 if ((cutenure==1 | cutenure==2 | cutenure==3) & (ref_year==1972 | ref_year==1973))
replace hflow=renteq*12 if (cutenure==1 & ref_year==1961)


*create new core consumption and total consumption measures 
replace cons5=(cons5*scale-(hflow_old/4)+(hflow/4))/scale  if (ref_year<=1979)
replace cons5=(cons5*scale-hflow_old+hflow)/scale if (ref_year>=1980)
replace cons6=(cons6*scale-(hflow_old/4)+(hflow/4))/scale if (ref_year<=1979)
replace cons6=(cons6*scale-hflow_old+hflow)/scale if (ref_year>=1980)

*drop years for which we do not have all observations
drop if ref_year==1979
drop if ref_year==2013

*ensure summation in Total Vehicle Cons works correctly
foreach var of varlist vehicpq vehiccq cartknpq cartkncq cartkupq cartkucq caropq carocq {
replace `var' = 0 if (`var'==. & ref_year>=1980) 
}

*Generate component totals to create different measures of consumption
* food at home
gen fdhome_tot = .
replace fdhome_tot = fdhome/4 if (ref_year<1970)
replace fdhome_tot = d_fdhome/4 if (ref_year>=1970 & ref_year<=1979) 
replace fdhome_tot = fdhomepq2+fdhomecq2 if (ref_year >= 1980)
*transportation 
gen trans_tot = .
replace trans_tot = (publictransother+automobileops+n_tot_vflow1)/4 if(ref_year<1970) 
replace trans_tot = (d_gas + d_othvehic-d_autofin+d_pubtrans+n_tot_vflow1)/4 if (ref_year>=1970 & ref_year<=1979)
replace trans_tot = (transpq+transcq+n_tot_vflow1-(vehfinpq+vehfincq+vehicpq+vehiccq+cartknpq+cartkncq+cartkupq+cartkucq+caropq+carocq)) if (ref_year>=1980)
*utilities 
gen util_tot = .
replace util_tot = utilites/4 if (ref_year<1970) 
replace util_tot = d_utility/4 if (ref_year>=1970 & ref_year<=1979) 
replace util_tot = utilpq+utilcq if (ref_year>=1980) 
*Adjusted utilities from predictions 
*replace util_tot = n_adj_util_floor if (cutenure==4 & _merge_rnt==3)
*housing
gen hous_tot = . 
replace hous_tot = (totrentdwe +hflow)/4 if (ref_year<1970) 
replace hous_tot = (d_rent + hflow)/4 if (ref_year>=1970 & ref_year<=1979) 
replace hous_tot = rendwepq+rendwecq+hflow if (ref_year>=1980) 
*difference in adjusted and reported utilities is utility payment included in rent (forced to be positive) 
*gen diff_rnt = n_adj_util_floor - (utilpq+utilcq) if (cutenure==4 & _merge_rnt==3) 
*replace hous_tot = hous_tot - diff_rnt if (cutenure==4 & _merge_rnt==3 & diff_rnt>0 & diff_rnt!=.) 
 
*Recode cons5 to drop all elements of transportation that are not well-measured
*AND get rid of public housing
rename cons5 uncorrected_cons
gen double cons5 = .
replace cons5= ((uncorrected_cons*scale)+(n_tot_vflow1/4)+gas_mo-trans_tot)/scale if (ref_year<1980)
replace cons5=((uncorrected_cons*scale)+n_tot_vflow1+gas_mo-trans_tot)/scale if (ref_year>=1980)
replace cons5=((cons5*scale)-(tflow2-tflow))/scale if (ref_year>=1980)
*Generate core less food, non-housing core, and core less food and utilities 
gen double cons10 = ((cons5*scale)-fdhome_tot)/scale 
gen double cons11 = ((cons5*scale)-hous_tot)/scale 
gen double cons12 = ((cons10*scale)-util_tot)/scale
gen double cons13 = ((cons5*scale)-gas_mo)/scale
gen double cons14 = ((cons5*scale)-(n_tot_vflow1/4))/scale if (ref_year<1980)
replace cons14 = ((cons5*scale)-n_tot_vflow1)/scale if (ref_year>=1980)

*Generate cpi defalator
gen cpi_u_rs_adj_11=.
replace cpi_u_rs_adj_11=4.263 if (ref_year==1960)
replace cpi_u_rs_adj_11=4.266 if (ref_year==1961)
replace cpi_u_rs_adj_11=4.27 if (ref_year==1962)
replace cpi_u_rs_adj_11=4.259 if (ref_year==1963)
replace cpi_u_rs_adj_11=4.248 if (ref_year==1964)
replace cpi_u_rs_adj_11=4.228 if (ref_year==1965)
replace cpi_u_rs_adj_11=4.156 if (ref_year==1966)
replace cpi_u_rs_adj_11=4.075 if (ref_year==1967)
replace cpi_u_rs_adj_11=3.962 if (ref_year==1968)
replace cpi_u_rs_adj_11=3.834 if (ref_year==1969)
replace cpi_u_rs_adj_11=3.693 if (ref_year==1970)
replace cpi_u_rs_adj_11=3.575 if (ref_year==1971)
replace cpi_u_rs_adj_11=3.509 if (ref_year==1972)
replace cpi_u_rs_adj_11=3.337 if (ref_year==1973)
replace cpi_u_rs_adj_11=3.064 if (ref_year==1974)
replace cpi_u_rs_adj_11=2.86 if (ref_year==1975)
replace cpi_u_rs_adj_11=2.733 if (ref_year==1976) 
replace cpi_u_rs_adj_11=2.597 if (ref_year==1977)
replace cpi_u_rs_adj_11=2.449 if (ref_year==1978)
replace cpi_u_rs_adj_11=2.252 if (ref_year==1979)
replace cpi_u_rs_adj_11=2.041 if (ref_year==1980) 
replace cpi_u_rs_adj_11=1.878 if (ref_year==1981)
replace cpi_u_rs_adj_11=1.784 if (ref_year==1982)
replace cpi_u_rs_adj_11=1.724 if (ref_year==1983)
replace cpi_u_rs_adj_11=1.669 if (ref_year==1984)
replace cpi_u_rs_adj_11=1.627 if (ref_year==1985)
replace cpi_u_rs_adj_11=1.61 if (ref_year==1986)
replace cpi_u_rs_adj_11=1.57 if (ref_year==1987) 
replace cpi_u_rs_adj_11=1.526 if (ref_year==1988)
replace cpi_u_rs_adj_11=1.474 if (ref_year==1989)
replace cpi_u_rs_adj_11=1.415 if (ref_year==1990)
replace cpi_u_rs_adj_11=1.377 if (ref_year==1991)
replace cpi_u_rs_adj_11=1.353 if (ref_year==1992)
replace cpi_u_rs_adj_11=1.331 if (ref_year==1993)
replace cpi_u_rs_adj_11=1.313 if (ref_year==1994)
replace cpi_u_rs_adj_11=1.293 if (ref_year==1995)
replace cpi_u_rs_adj_11=1.269 if (ref_year==1996)
replace cpi_u_rs_adj_11=1.252 if (ref_year==1997)
replace cpi_u_rs_adj_11=1.245 if (ref_year==1998)
replace cpi_u_rs_adj_11=1.229 if (ref_year==1999) 
replace cpi_u_rs_adj_11=1.198 if (ref_year==2000)
replace cpi_u_rs_adj_11=1.175 if (ref_year==2001)
replace cpi_u_rs_adj_11=1.165 if (ref_year==2002)
replace cpi_u_rs_adj_11=1.149 if (ref_year==2003)
replace cpi_u_rs_adj_11=1.127 if (ref_year==2004)
replace cpi_u_rs_adj_11=1.099 if (ref_year==2005)
replace cpi_u_rs_adj_11=1.073 if (ref_year==2006)
replace cpi_u_rs_adj_11=1.051 if (ref_year==2007)
replace cpi_u_rs_adj_11=1.02 if (ref_year==2008) 
replace cpi_u_rs_adj_11=1.032 if (ref_year==2009) 
replace cpi_u_rs_adj_11=1.024 if (ref_year==2010) 
replace cpi_u_rs_adj_11=1 if (ref_year==2011)
replace cpi_u_rs_adj_11=.987 if (ref_year==2012)

*Generate real consumption figures
foreach var of varlist cons* {
gen r_`var' = `var'*cpi_u_rs_adj_11
}

label var r_cons5 "Well measured consumption (core)" 
label var r_cons6 "Total Consumption"
label var r_cons10 "Core less food" 
label var r_cons11 "Non-housing core"
label var r_cons12 "Core less food and utilities"
label var r_cons13 "Core less gas and motor oil"
label var r_cons14 "Core less vehicle"

*Collapse data on the 10th, 50th, and 90th percentiles, by year
preserve 
collapse (p10) p10cons5=r_cons5 p10cons10=r_cons10 p10cons11=r_cons11 p10cons12=r_cons12 p10cons13=r_cons13 p10cons14=r_cons14 p10cons6=r_cons6 (p50) medcons5=r_cons5 medcons10=r_cons10 medcons11=r_cons11 medcons12=r_cons12 medcons13=r_cons13 medcons14=r_cons14 medcons6=r_cons6 (p90) p90cons5=r_cons5 p90cons10=r_cons10 p90cons11=r_cons11 p90cons12=r_cons12 p90cons13=r_cons13 p90cons14=r_cons14 p90cons6=r_cons6 [w=wgt20] , by(ref_year)

*generate ratios 
foreach x in "cons5" "cons10" "cons11" "cons12" "cons13" "cons14" "cons6" { 
gen ratio`x'_90_10 = p90`x'/p10`x'
gen ratio`x'_90_50 = p90`x'/med`x'
gen ratio`x'_50_10 = med`x'/p10`x'
}
*export to excel for cleaning up and presenation 
export excel using cons_inequality_data_updated`y'.xls , firstrow(var) replace 

restore 
save inequality_data_for_`y' , replace 
}
log close 
